﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Entity;
using System.Data.OracleClient;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Security.AccessControl;
using System.Security.Cryptography;
using System.Security.Principal;
using System.Text;
using System.Windows.Forms;
using Dapper;
using Newtonsoft.Json;
using SqlSugar;
using Database = PetaPoco.Database;
using DbType = SqlSugar.DbType;
using OracleParameter = Oracle.ManagedDataAccess.Client.OracleParameter;

namespace WindowsFormsTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            //GetCurrentFolderUsersPermissions();
        }

        private void btnEnter_Click(object sender, EventArgs e)
        {
            Dictionary<string, object> parm = new Dictionary<string, object>();
            parm.Add("status", 0); //在线记录。

            //string ret = WebAPIHelper.Post(Program.client.APIURL, "KZ_SFCAPI_WorkOrder", "KZ_SFCAPI_WorkOrder.Controllers.GeneralServer", "GetOnlineAndOfflineDepts", Program.client.UserToken, JsonConvert.SerializeObject(parm));

            //if (ret != null)
            //{
            //    var retJson = JsonConvert.DeserializeObject<Dictionary<string, object>>(ret);
            //    if (Convert.ToBoolean(retJson["IsSuccess"]))
            //    {
            //        string json = retJson["RetData"].ToString();
            //        DataTable dtDeptNos = JsonConvert.DeserializeObject<DataTable>(json);
            //        if (dtDeptNos != null && dtDeptNos.Rows.Count > 0)
            //        {
            //            foreach (DataRow row in dtDeptNos.Rows)
            //            {
            //                if (row != null)
            //                {
            //                    DataTable workTimeDt = GetWorkTimeSyncWorkHour(row["shiftCode"].ToString());
            //                }
            //            }

            //            MessageBox.Show("已成功加载所有的方法");
            //        }
            //    }
            //}
        }

        /// <summary>
        ///     获取当前文件夹所有用户权限
        /// </summary>
        private void GetCurrentFolderUsersPermissions()
        {
            string fileName = @"D:\NET5_API\2021-10日志\10-12.log";

            #region 以下代码基本没什么用

            string vLoginName = Environment.UserName;
            string vComputerName = WindowsIdentity.GetCurrent().Name;
            Console.WriteLine($"登录到操作系统的用户的用户名是{vLoginName}");
            Console.WriteLine($"获取用户的 Windows登录名是{vComputerName}");

            #endregion

            // 通过File创建FileSecurity对象
            FileSecurity acl = File.GetAccessControl(fileName);
            // 返回访问规则集合 
            AuthorizationRuleCollection rules = acl.GetAccessRules(true, true, typeof(NTAccount));
            // 遍历访问规则
            foreach (AuthorizationRule rule in rules)
            {
                // 输出规则
                Console.WriteLine(rule.IdentityReference);
                // 文件系统访问规则
                FileSystemAccessRule accessRule = rule as FileSystemAccessRule;
                if (accessRule != null)
                {
                    Console.WriteLine("  ...{0}", accessRule.FileSystemRights);
                }
            }

            Console.ReadLine();
        }


        /// <summary>
        ///     获取定时服务获取工时
        /// </summary>
        private DataTable GetWorkTimeSyncWorkHour(string shift_code)
        {
            Dictionary<string, object> parm = new Dictionary<string, object>();
            if (!string.IsNullOrWhiteSpace(shift_code))
            {
                parm.Add("shift_code", shift_code);
            }

            //string ret = WebAPIHelper.Post(Program.client.APIURL, "KZ_SFCAPI_WorkOrder", "KZ_SFCAPI_WorkOrder.Controllers.GeneralServer", "GetWorkTimeSyncWorkHour", Program.client.UserToken, JsonConvert.SerializeObject(parm));
            //var retJson = JsonConvert.DeserializeObject<Dictionary<string, object>>(ret);

            //if (Convert.ToBoolean(retJson["IsSuccess"]))
            //{
            //    string json = retJson["RetData"].ToString();
            //    DataTable dtJson = JsonConvert.DeserializeObject<DataTable>(json);
            //    return dtJson;
            //}
            //else
            //{
            //    MessageBox.Show($"出错信息是GetWorkTimeSyncWorkHour方法：{retJson["ErrMsg"]}");
            //    return null;
            //}

            return null;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string str = "";

            if (string.IsNullOrEmpty(str)) //IsNullOrEmpty判断value==null、value=""。
            {
                bool flag = true;
            }

            if (string.IsNullOrWhiteSpace(str))
            {
                bool flag = true;
            }

            string str1 = "  ";
            if (string.IsNullOrEmpty(str1))
            {
                bool flag = true;
            }

            if (string.IsNullOrWhiteSpace(str1))
            {
                bool flag = true;
            }

            string str2 = string.Empty;
            if (string.IsNullOrEmpty(str2))
            {
                bool flag = true;
            }

            if (string.IsNullOrWhiteSpace(str2)) //IsNullOrWhiteSpace判断value==null、value="",value="  "为空。
            {
                bool flag = true;
            }
        }

        private void btnOracleTest_Click(object sender, EventArgs e)
        {
            try
            {
                //System.Data.OracleClient使用4.0没有错误，2.0会报OracleClient使用需要8.17客户端以上版本错误。
                string user = "mes00";
                string pass = "dbmes00";
                string server = "MESTEST";

                if (user.Trim() == "" || server.Trim() == "")
                {
                    MessageBox.Show(this, "用户名或密码不能为空！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }

                string constr = "Data Source=" + server + ";user id=" + user + ";password=" + pass;

                //测试连接
                OracleConnection myCn = new OracleConnection(constr);
                try
                {
                    Text = "正在连接服务器，请稍候...";
                    myCn.Open();
                }
                catch (Exception ex)
                {
                    Text = "连接服务器失败！";
                    myCn.Close();
                    MessageBox.Show(this, $"{ex.Message}", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                myCn.Close();
                Text = "连接服务器成功！";

                MessageBox.Show(this, "连接成功", "提示", MessageBoxButtons.OK);
                DialogResult = DialogResult.OK;
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        /// <summary>
        ///     手机上的缓存，.m3u8.sqlite文件转换为视频文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnTencentConvert_Click(object sender, EventArgs e)
        {
            #region 打开选项框

            //openFileDialog1.InitialDirectory = @"E:\";//默认路径
            //openFileDialog1.Filter = "All files (*.*)|*.*";
            //openFileDialog1.RestoreDirectory = true;//但打开对话框后，文件内容有改变了，是否同步刷新
            //openFileDialog1.FilterIndex = 1;//当filter有多个时，选择默认的filter，注意，下标时从1开始，如果只有一个filter可以不用写这个属性
            //if (openFileDialog1.ShowDialog() == DialogResult.OK)//这个是关键，意思是当你选择了文件后并点击了OK按钮
            //{
            //    fileName = openFileDialog1.FileName;//获取选中文件的路径是通过FileName属性来获得
            //}

            #endregion

            try
            {
                string pathSource = @"E:\腾讯课堂朝夕教育\测试sqlite视频文件";
                string[] fileInfo = Directory.GetFiles(pathSource);
                //FileInfo[] filesNames = new DirectoryInfo(pathSource).GetFiles();

                byte[] keyBytes = new byte[16];
                for (int i = 0; i < fileInfo.Length; i++)
                {
                    if (!fileInfo[i].Contains("sqlite"))
                    {
                        //return;//return的话，MessageBox.Show("转化成功！")不会执行。
                        break;
                    }

                    int allNum = i + 1;
                    MessageBox.Show($"提取解密进度：{allNum}/{fileInfo.Length}");
                    //string pathSource2 = @"E:\腾讯课堂朝夕教育\测试sqlite视频文件\1.m3u8.sqlite";
                    //SQLiteConnection sqliteConnection = new SQLiteConnection("Data Source=" + pathSource2 + ";Initial Catalog=main;Integrated Security=True;Max Pool Size=10");
                    SQLiteConnection sqliteConnection = new SQLiteConnection("Data Source=" + fileInfo[i] + ";Initial Catalog=main;Integrated Security=True;Max Pool Size=10");
                    sqliteConnection.Open();
                    SQLiteCommand cmd = new SQLiteCommand();
                    cmd.Connection = sqliteConnection;
                    cmd.CommandText = @"select value FROM caches where key like '%get_dk%'";
                    SQLiteDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        //key2 = ObjectToBytes(dr[0]);//ObjectToBytes这个转化有问题，会报指定键的大小对于此算法无效错误。这里必须是16位长度的key字节数。需要显示指定为16位byte数组。
                        dr.GetBytes(0, 0L, keyBytes, 0, 16);
                    }

                    dr.Close();

                    cmd.CommandText = @"select key FROM caches where key like '%start%'";
                    SQLiteDataReader dr2 = cmd.ExecuteReader();

                    List<string> startList = new List<string>();

                    while (dr2.Read())
                    {
                        //string results = dr2[0].ToString();
                        //https://1258712167.vod2.myqcloud.com/fb8e6c92vodtranscq1258712167/9379b7a35285890793753887628/drm/v.f30742.ts?start=0&end=1100943&type=mpegts
                        //string strIndex = results.Substring(0, results.IndexOf("&end"));
                        //https://1258712167.vod2.myqcloud.com/fb8e6c92vodtranscq1258712167/9379b7a35285890793753887628/drm/v.f30742.ts?start=0
                        //int startIndex = strIndex.IndexOf("start=");
                        //117
                        //strIndex = strIndex.Substring(startIndex, length - startIndex);
                        //start = 0                      
                        //strIndex = strIndex.Replace("start=", "");
                        //0
                        string results = dr2[0].ToString();
                        string strIndex = results.Substring(0, results.IndexOf("&end"));
                        int startIndex = strIndex.IndexOf("start=");
                        int length = strIndex.Length;
                        strIndex = strIndex.Substring(startIndex, length - startIndex);
                        strIndex = strIndex.Replace("start=", "");
                        startList.Add(strIndex);

                        //string key = dr2[0].ToString();
                        //https://1258712167.vod2.myqcloud.com/fb8e6c92vodtranscq1258712167/9379b7a35285890793753887628/drm/v.f30742.ts?start=1003046000&end=1004167807&type=mpegts
                        //string startValue = key.Substring(0, key.IndexOf("&end"));
                        //https://1258712167.vod2.myqcloud.com/fb8e6c92vodtranscq1258712167/9379b7a35285890793753887628/drm/v.f30742.ts?start=1003046000
                        //int index = key.IndexOf("=");
                        //startValue = startValue.Substring(index + 1);
                        //1003046000

                        //string key = dr2[0].ToString();
                        //string startValue = key.Substring(0, key.IndexOf("&end"));
                        //int index = key.IndexOf("=");
                        //startValue = startValue.Substring(index + 1);
                        //startList.Add(startValue);
                    }

                    dr2.Close();

                    startList.Sort();
                    string path = fileInfo[i].Replace(".m3u8.sqlite", "") + ".ts";
                    FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write);
                    BinaryWriter bw = new BinaryWriter(fs);
                    for (int j = 0; j < startList.Count; j++)
                    {
                        byte[] ts_encode = null;
                        cmd.Connection = sqliteConnection;
                        cmd.CommandText = $"select value FROM caches where key like '%start={startList[j]}%'";
                        SQLiteDataReader dr3 = cmd.ExecuteReader();
                        while (dr3.Read())
                        {
                            //ts_encode_con = ObjectToBytes(dr3[0].ToString());
                            //Decode(key,ts_encode, results);
                            ts_encode = new byte[dr3.GetBytes(0, 0L, null, 0, int.MaxValue)];
                            dr3.GetBytes(0, 0L, ts_encode, 0, ts_encode.Length);
                            bw.Write(AESDecrypt(keyBytes, keyBytes, ts_encode)); //写入文件
                        }

                        dr3.Close();
                    }

                    sqliteConnection.Close();
                    bw.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"转化失败！错误信息是：{ex.Message}");
            }
            finally
            {
                MessageBox.Show("转化成功！");
            }

            //MessageBox.Show("转化成功！");//前面try里面有return，可能不会执行，如果是在try...catch...外面，没有finally，那么不会执行。如果有没有finally并且在里面，那么会执行。
        }

        /// <summary>
        ///     将一个object对象序列化，返回一个byte[]
        /// </summary>
        /// <param name="obj">能序列化的对象</param>
        /// <returns></returns>
        public static byte[] ObjectToBytes(object obj)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                IFormatter formatter = new BinaryFormatter();
                formatter.Serialize(ms, obj);
                return ms.GetBuffer();
            }
        }

        private void Decode(byte[] key, byte[] ts_encode_con, string fileName)
        {
            //可以指定盘符，也可以指定任意文件名，还可以为word等文件
            // 创建文件
            //string text = ".ts";
            //string path = fileName + text;
            string path = fileName;
            //FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite);//如果文件存在文件字节比较大，重新加载比较小，也不会改变文件大小
            //FileStream fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);//如果文件存在文件字节比较大，重新加载比较小，也不会改变文件大小，因为已经存在，没有读权限。只能创建。但是可能也会更新错误。就看拿到的是什么权限。但是最终原因也未能知道。在本地计算机可以测试通过，没有读权限。
            //FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write);
            //StreamWriter sw = new StreamWriter(fs); //创建写入流
            ////sw.Write(AESDecrypt(ts_encode_con, key)); //写入文件
            //sw.Write(AES128Decrypt(key, ts_encode_con)); //写入文件
            //sw.Close();//关闭文件  //这个StreamWriter和BinaryWriter解压出来的不一样，不能播放的。

            //StreamWriter sw = new StreamWriter(fs); //创建写入流
            //sw.Write(Decrypt(key, ts_encode_con)); //写入文件
            ////sw.Write(AES128Decrypt(key, ts_encode_con)); //写入文件
            //sw.Close();//关闭文件 //这个StreamWriter和BinaryWriter解压出来的不一样，不能播放的。

            //StreamWriter sw = new StreamWriter(fs); //创建写入流
            //sw.Write(AESDecrypt(key, key, ts_encode_con)); //写入文件
            //sw.Close();//关闭文件 //这个StreamWriter和BinaryWriter解压出来的不一样，不能播放的。

            FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write);
            BinaryWriter bw = new BinaryWriter(fs); //创建写入流，必须是BinaryWriter，不然解压出来的视频虽然比较大，但是字节数不对，打不开。
            //bw.Write(Decrypt(key, ts_encode_con)); //写入文件
            //bw.Write(AESDecrypt128(key, ts_encode_con)); //写入文件
            //bw.Write(AES128Decrypt(key, key, ts_encode_con)); //写入文件
            //bw.Write(AESDecrypt(key, ts_encode_con)); //写入文件
            //bw.Write(AES128Decrypt(key, ts_encode_con)); //写入文件
            bw.Write(AESDecrypt(key, key, ts_encode_con)); //写入文件
            //bw.Write(AESDecryptStr(key, key, ts_encode_con)); //写入文件
            bw.Close(); //关闭文件
        }

        /// <summary>
        ///     AES解密
        /// </summary>
        /// <param name="text"></param>
        /// <param name="password"></param>
        /// <param name="iv"></param>
        /// <returns></returns>
        public byte[] AESDecrypt(byte[] pwdBytes, byte[] ivBytes, byte[] encryptedData)
        {
            RijndaelManaged rijndaelCipher = new RijndaelManaged();
            rijndaelCipher.Mode = CipherMode.CBC;
            rijndaelCipher.Padding = PaddingMode.PKCS7;
            rijndaelCipher.KeySize = 128;
            rijndaelCipher.BlockSize = 128;
            //byte[] encryptedData = Convert.FromBase64String(text);
            // byte[] pwdBytes = System.Text.Encoding.UTF8.GetBytes(password);
            byte[] keyBytes = new byte[16];
            int len = pwdBytes.Length;
            if (len > keyBytes.Length) len = keyBytes.Length;
            Array.Copy(pwdBytes, keyBytes, len);
            rijndaelCipher.Key = keyBytes;
            //byte[] ivBytes = Encoding.UTF8.GetBytes(iv);
            rijndaelCipher.IV = ivBytes;
            ICryptoTransform transform = rijndaelCipher.CreateDecryptor();
            byte[] plainText = transform.TransformFinalBlock(encryptedData, 0, encryptedData.Length);
            return plainText;
            //return Encoding.UTF8.GetString(plainText);//转换虽然成功，但是不能播放。无论是StreamWriter还是BinaryWriter都是有问题的，不能播放。
        }

        /// <summary>
        ///     AES解密
        /// </summary>
        /// <param name="text"></param>
        /// <param name="password"></param>
        /// <param name="iv"></param>
        /// <returns></returns>
        public string AESDecryptStr(byte[] pwdBytes, byte[] ivBytes, byte[] encryptedData)
        {
            RijndaelManaged rijndaelCipher = new RijndaelManaged();
            rijndaelCipher.Mode = CipherMode.CBC;
            rijndaelCipher.Padding = PaddingMode.PKCS7;
            rijndaelCipher.KeySize = 128;
            rijndaelCipher.BlockSize = 128;
            //byte[] encryptedData = Convert.FromBase64String(text);
            // byte[] pwdBytes = System.Text.Encoding.UTF8.GetBytes(password);
            byte[] keyBytes = new byte[16];
            int len = pwdBytes.Length;
            if (len > keyBytes.Length) len = keyBytes.Length;
            Array.Copy(pwdBytes, keyBytes, len);
            rijndaelCipher.Key = keyBytes;
            //byte[] ivBytes = Encoding.UTF8.GetBytes(iv);
            rijndaelCipher.IV = ivBytes;
            ICryptoTransform transform = rijndaelCipher.CreateDecryptor();
            byte[] plainText = transform.TransformFinalBlock(encryptedData, 0, encryptedData.Length);
            return Encoding.UTF8.GetString(plainText); //转换虽然成功，但是不能播放。无论是StreamWriter还是BinaryWriter都是有问题的，不能播放。
        }


        public static byte[] AES128Decrypt(byte[] keyByte, byte[] ivByte, byte[] encryptedBuff, CipherMode mode = CipherMode.CBC, PaddingMode padding = PaddingMode.PKCS7)
        {
            byte[] inBuff = encryptedBuff;
            Aes dcpt = Aes.Create();
            dcpt.BlockSize = 128;
            dcpt.KeySize = 128;
            dcpt.Key = keyByte;
            dcpt.IV = ivByte;
            dcpt.Mode = mode;
            dcpt.Padding = padding;

            ICryptoTransform cTransform = dcpt.CreateDecryptor();
            byte[] resultArray = cTransform.TransformFinalBlock(inBuff, 0, inBuff.Length);
            return resultArray;
        }

        /// <summary>
        ///     AES解密(AES128位解密)：测试成功解密但不能播放正确的文件，也是错误的！
        /// </summary>
        /// <param name="encrypted"></param>
        /// <returns></returns>
        public static byte[] AESDecrypt(byte[] keyBytes, byte[] encrypted) //因为必须是byte[]数组返回值
        {
            using (RijndaelManaged cipher = new RijndaelManaged())
            {
                cipher.Mode = CipherMode.CBC;
                cipher.Padding = PaddingMode.PKCS7;
                cipher.KeySize = 128;
                cipher.BlockSize = 128;
                cipher.Key = keyBytes;
                cipher.IV = keyBytes;

                //List<byte> lstBytes = new List<byte>();
                //for (int i = 0; i < encrypted.Length; i++)
                //{
                //    lstBytes.Add(encrypted[i]);
                //}

                using (ICryptoTransform decryptor = cipher.CreateDecryptor())
                {
                    //using (MemoryStream msDecrypt = new MemoryStream(lstBytes.ToArray()))
                    //{
                    //    using (CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read))
                    //    {
                    //        using (StreamReader srDecrypt = new StreamReader(csDecrypt))
                    //        {
                    //            return srDecrypt.ReadToEnd();
                    //        }
                    //    }
                    //}

                    byte[] resultArray = decryptor.TransformFinalBlock(encrypted, 0, encrypted.Length);
                    return resultArray; //直接返回是可以的。
                }
            }
        }


        /// <summary>
        ///     AES解密(AES128位解密)
        /// </summary>
        /// <param name="encrypted"></param>
        /// <returns></returns>
        public static byte[] AESDecrypt128(byte[] keyBytes, byte[] encrypted)
        {
            using (RijndaelManaged cipher = new RijndaelManaged())
            {
                cipher.Mode = CipherMode.CBC;
                cipher.Padding = PaddingMode.PKCS7;
                cipher.KeySize = 128;
                cipher.BlockSize = 128;
                cipher.Key = keyBytes;
                cipher.IV = keyBytes;
                using (ICryptoTransform decryptor = cipher.CreateDecryptor())
                {
                    //using (MemoryStream msDecrypt = new MemoryStream(encrypted))
                    //{
                    //using (CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read))
                    //{
                    //    using (StreamReader srDecrypt = new StreamReader(csDecrypt)) //这个不行
                    //    {
                    //        return srDecrypt.ReadToEnd();
                    //    }
                    //}
                    //}

                    byte[] resultArray = decryptor.TransformFinalBlock(encrypted, 0, encrypted.Length);
                    return resultArray; //直接返回是可以的。
                }
            }
        }

        /// <summary>
        ///     AES解密
        /// </summary>
        /// <param name="decryptStr">密文</param>
        /// <param name="key">密钥</param>
        /// <returns></returns>
        public static byte[] Decrypt(byte[] keyArray, byte[] toEncryptArray)
        {
            //byte[] keyArray = UTF8Encoding.UTF8.GetBytes(key);

            //byte[] toEncryptArray = Convert.FromBase64String(decryptStr);

            RijndaelManaged rDel = new RijndaelManaged();
            rDel.Key = keyArray;
            //rDel.Mode = CipherMode.ECB; //这个必须去掉,改为CipherMode.CBC,或者，两个属性Mode和Padding注释去掉即可。
            rDel.Mode = CipherMode.CBC;
            rDel.Padding = PaddingMode.PKCS7;

            ICryptoTransform cTransform = rDel.CreateDecryptor();

            byte[] resultArray = cTransform.TransformFinalBlock(toEncryptArray, 0, toEncryptArray.Length);

            return resultArray; //直接返回是可以的。
            //return UTF8Encoding.UTF8.GetString(resultArray);
        }

        /// <summary>
        ///     反编译的破解解压秘钥方法
        /// </summary>
        /// <param name="key"></param>
        /// <param name="filecontext"></param>
        /// <returns></returns>
        public byte[] AES128Decrypt(byte[] key, byte[] filecontext)
        {
            RijndaelManaged rijndaelManaged = new RijndaelManaged();
            rijndaelManaged.Key = key;
            return rijndaelManaged.CreateDecryptor().TransformFinalBlock(filecontext, 0, filecontext.Length);
        }

        private void btnOracle_Click(object sender, EventArgs e)
        {
            OracleDbContext dbContext = new OracleDbContext();
            //ef生成sql日志跟踪 https://www.cnblogs.com/huangxincheng/p/5911448.html
            //第一种设置方法：
            dbContext.Database.Log = Console.WriteLine;
            //dbContext.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
            var query = from p in dbContext.OrderTest where p.ID > 0 select p;

            //ObjectQuery<OrderTest> orderTests = query as ObjectQuery<OrderTest>;
            //if (orderTests!=null)
            //{
            //    var sql1 = orderTests.ToTraceString();
            //}
            //上面的不行。orderTests转换为null，转换失败。

            string sql = query.ToString(); //from实现的linq方式可以生成对应的sql语句。
            var list = dbContext.OrderTest.Where(i => i.ID > 0).ToList();
            int id = 1;
            //var sql2=dbContext.OrderTests.Where(i => i.ID ==1).AsQueryable().ToString();//打印生成的sql语句,SQL语句直接带值
            var sql2 = dbContext.OrderTest.Where(i => i.ID == id).AsQueryable().ToString(); //打印生成的sql语句,SQL语句直接带参数。
            Console.WriteLine(sql2);
            var list2 = sql2.ToList(); //查询结果返回值。
        }

        private void btnSimpleOracle_Click(object sender, EventArgs e)
        {
            OracleDbContext dbContext = new OracleDbContext();
            int id = 1;
            DataTable dt3 = dbContext.Database.ExcuteDataTable("SELECT * FROM ORDERTEST WHERE ID=:p0", id);
            OracleParameter parms = new OracleParameter();
            parms.ParameterName = "id"; //这个可以不要。但是不要的话，就没有办法生成对应的SQL语句。
            parms.Value = id;
            DataTable dt2 = dbContext.Database.ExcuteDataTableByParam("SELECT * FROM ORDERTEST WHERE ID=:id", parms);
            string name = "马化腾2";
            //参数化查询，带参数。
            List<string> strList6 = dbContext.Database.SqlQuery<string>("SELECT Name FROM ORDERTEST WHERE ID={0} AND NAME={1}", id, name).ToList();
            //List<string> strList4 = dbContext.Database.SqlQuery<string>($"SELECT Name FROM ORDERTEST WHERE ID=:id", id).ToList();//可以使用
            //List<string> strList4 = dbContext.Database.SqlQuery<string>($"SELECT Name FROM ORDERTEST WHERE ID=:{id}", id).ToList();//可以使用
            List<string> strList4 = dbContext.Database.SqlQuery<string>("SELECT Name FROM ORDERTEST WHERE ID={0}", id).ToList(); //可以使用,但是要在DBlogFormatter里面设置把参数@换成：。
            List<string> strList5 = dbContext.Database.SqlQuery<string>("SELECT Name FROM ORDERTEST WHERE ID=:p0", id).ToList(); //参数必须命名为p开头，然后按照数字递增，这样就可以打印对应的SQL语句出来了。

            //var sql1 = dbContext.OrderTest.Where(i => i.ID == id).AsQueryable().ToString(); //打印生成的sql语句,SQL语句直接带值
            //var list1 = sql1.ToList(); //查询结果返回值。这个不会输出SQL语句
            //var list2 = dbContext.OrderTest.Where(i => i.ID == id && i.NAME==name).ToList(); //查询结果返回值。这个会输出SQL语句，并带参数和参数值。
            var query = from p in dbContext.OrderTest where p.ID == id && p.NAME == name select p;
            var list = query.ToList(); //必须调用ToList才会生成对应的SQL语句打印日志出来。
            //string sql = query.ToString(); //from实现的linq方式可以生成对应的sql语句。
            //Console.WriteLine(sql);
            List<string> strList = dbContext.Database.SqlQuery<string>($"SELECT Name FROM ORDERTEST WHERE ID={id}").ToList(); //这个生成的SQL语句不会被参数化
            dbContext.OrderTest.Load(); //调用SQL语句，但不保存数据在本地变量中。

            //List<object> strList2 = dbContext.Database.SqlQuery<object>($"SELECT * FROM ORDERTEST WHERE ID={id}").ToList();//这个生成的SQL语句不会被参数化。
            //Console.WriteLine(JsonConvert.SerializeObject(strList2));//都为空值
            //List<dynamic> strList3 = dbContext.Database.SqlQuery<dynamic>($"SELECT * FROM ORDERTEST WHERE ID={id}").ToList();//这个生成的SQL语句不会被参数化。
            //Console.WriteLine(JsonConvert.SerializeObject(strList3));//都为空值

            DataTable dt = dbContext.Database.SqlQueryForDataTable($"SELECT * FROM ORDERTEST WHERE ID={id}");
            DataTable dtEF = dbContext.Database.SqlQueryForDataTableByEF($"SELECT * FROM ORDERTEST WHERE ID={id}");

            string name2 = "马化腾3";
            id = 3;
            string addSql = @"INSERT INTO ORDERTEST VALUES(:Id,:Name)";
            //int count2 = dbContext.Database.ExecuteSqlCommand(addSql, new Oracle.ManagedDataAccess.Client.OracleParameter(":Id", id), new Oracle.ManagedDataAccess.Client.OracleParameter(":Name", name2));

            name2 = "马化腾2";
            string updateSql = "UPDATE ORDERTEST SET Name=:Name WHERE ID=:Id";
            //int count = dbContext.Database.ExecuteSqlCommand(updateSql, new Oracle.ManagedDataAccess.Client.OracleParameter(":Name", name2), new Oracle.ManagedDataAccess.Client.OracleParameter(":Id", id));
            string deleteSql = "DELETE ORDERTEST WHERE ID=:Id";
            //int count3 = dbContext.Database.ExecuteSqlCommand(deleteSql, new Oracle.ManagedDataAccess.Client.OracleParameter(":Id", id));

            int count5 = dbContext.Database.ExecuteSqlCommand(updateSql, name2, id); //测试通过
            //int count4 = dbContext.Database.ExecuteSqlCommand(deleteSql,id);//测试通过
        }

        private void btnDapper_Click(object sender, EventArgs e)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString;
            using (IDbConnection con = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString))
            {
                int id = 1;
                string name = "马化腾2";
                int age = 10;
                string sql = "SELECT * FROM ORDERTEST WHERE ID=:id AND NAME=:name AND age=:age";
                int pageIndex = 1;
                int pageSize = 20; //每页多少行。
                //var test=con.Get<OrderTest>(id);//Dapper.SimpleCRUD只支持SQL Server,因为它的参数名是@，不支持：，Oracle会报错。
                //List<OrderTest> pageList= con.GetListPaged<OrderTest>(pageIndex, pageSize, "WHERE ID=:id", "id asc",new{id}).ToList();

                //List<OrderTest> pageList = con.GetListPaged<OrderTest>(pageIndex, pageSize, "WHERE ID=1", "ID asc").ToList();
                DapperSQLFormatter.GetSql(sql, new { id, name, age });
                DapperSQLFormatter.GetSqlByParams(sql, id, name, age); //全局设置可在Dapper源代码里面加入这个类方法。
                List<OrderTest> list = con.Query<OrderTest>(sql, new { id, name, age }).ToList();

                sql = "SELECT * FROM ORDERTEST WHERE ID=:id AND NAME=:name";
                OrderTest model = con.Query<OrderTest>(sql, new { id, name }).FirstOrDefault(); //这两种使用都可以。
                OrderTest model2 = con.QueryFirstOrDefault<OrderTest>(sql, new { id, name });
                int count = con.Execute(sql, new { id, name }); //插入、更新、删除。
            }
        }

        private void btnPetapoco_Click(object sender, EventArgs e)
        {
            var db = new Database("OracleDbContext");
            int id = 1;
            string name = "马化腾2";
            int age = 0;
            string sql = "SELECT * FROM ORDERTEST WHERE ID=@0"; //即使是Oracle数据库，也是用@符号，而不是Oracle的参数符号：
            var model = db.Query<OrderTest>(sql, id).ToList();
            sql = "SELECT * FROM ORDERTEST WHERE ID=@0 AND NAME=@1 AND age=@2";
            var model2 = db.Query<OrderTest>(sql, id, name, age).ToList();
            string lastSQL = db.LastSQL;
            string lastCommand = db.LastCommand; //sql语句，带输出参数值
            object[] argsValue = db.LastArgs; //参数值;
            DapperSQLFormatter.GetSqlByParams(lastSQL, argsValue); //全局设置可在Dapper源代码里面加入这个类方法。在Petapoco也可以通用。
        }

        private void btnSqlSugar_Click(object sender, EventArgs e)
        {
            ConnectionConfig connectionConfig = new ConnectionConfig();
            connectionConfig.ConnectionString = ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString;
            connectionConfig.DbType = DbType.Oracle;
            SqlSugarClient db = new SqlSugarClient(connectionConfig);
            //Aop一定要设置在你操作语句之前，不然不会生效，还有必须是同一个SqlSuagrClient才会有效。Aop监听sql，此段会在每一个"操作语句"执行时都进入。
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                //var parmValue = pars.ToDictionary(it => it.ParameterName, it => it.Value);
                //string sqlTemp = sql + "\n参数值：" + db.Utilities.SerializeObject(parmValue);
                //Console.WriteLine(sqlTemp);
                foreach (SugarParameter item in pars)
                {
                    sql = sql.Replace($"{item.ParameterName}", $"'{item.Value}'");
                }

                Console.WriteLine($"打印的SQL语句：{sql}");
            };

            db.Aop.OnError = exp => //SQL报错
            {
                //exp.sql 这样可以拿到错误SQL
                string errorSQL = exp.Sql;

                Console.WriteLine($"打印错误的SQL语句：{errorSQL}");
            };
            int id = 1;
            string name = "马化腾2";
            var model = db.Queryable<OrderTest>().Where(t => t.ID == id && t.NAME == name).ToList();

            var dt = db.Ado.GetDataTable("SELECT * FROM ORDERTEST and WHERE ID=:id", new { id = 1 });
        }
    }
}